package connection;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.Task;

public class TaskConnection extends ConnectionControl {
	
	public void addTaskToDB(Task oTask, int actId){
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		try {
			
			Statement statement = con.createStatement() ;
			
			statement.executeUpdate("INSERT INTO TASK(ID, DESCRIPTION, STARTDATE, ESTIMATEDHOURS, EXACTHOURS, DELIVERABLE, ACTIVITYID) VALUES("+oTask.getNumber()+", '"+oTask.getDesc()+"', '"+oTask.getStartDate()+"', "+oTask.getEstimatedHours()+", "+oTask.getExactHours()+", '"+oTask.getDeliverable()+"' ,"+ actId +")");
			System.out.println("it is ok");
			statement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
	}
	
	public void removeTaskFromDB(int id){
		
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		try {
			
			Statement stetement = con.createStatement() ;
			
			ResultSet rset = stetement.executeQuery("DELETE FROM TASK WHERE ID = "+id);

			rset.close();
			stetement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
	}
	
	
	public void updateTaskFromDB(int id,String desc,String startDate, int estimatedHours, int exactHours, String deliverable){
		
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		try {
			
			Statement stetement = con.createStatement() ;
			
			ResultSet rset = stetement.executeQuery("UPDATE TASK SET DESCRIPTION = '"+ desc +"',STARTDATE = '"+startDate+"' ,ESTIMATEDHOURS="+estimatedHours+", EXACTHOURS="+exactHours+",DELIVERABLE='"+deliverable+"' WHERE ID = "+id);

			rset.close();
			stetement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
	}
	
	public ArrayList<Task> getAllTasksFromDB(){
		
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		ArrayList<Task> oTaskList = new ArrayList<Task>();
		
		try {
			
			Statement stetement = con.createStatement() ;
			
			ResultSet rset = stetement.executeQuery("select * from task");
			
			while (rset.next()) {
				Task oTask = new Task();
				oTask.setNumber(rset.getInt(1));
				oTask.setDesc(rset.getString(2));
				oTask.setStartDate(rset.getString(3));
				oTask.setEstimatedHours(rset.getInt(4));
				oTask.setExactHours(rset.getInt(5));
				oTask.setDeliverable(rset.getString(6));
				
				oTaskList.add(oTask);
				}
			rset.close();
			stetement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
		
		return oTaskList;
		
	}
	
	public ArrayList<Task> getAllTaskofActivity(int activityId){
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		ArrayList<Task> oTaskList = new ArrayList<Task>();
		
		try {
			
			Statement stetement = con.createStatement() ;
			
			ResultSet rset = stetement.executeQuery("select * from task where activityid = "+ activityId);
			
			while (rset.next()) {
				Task oTask = new Task();
				
				oTask.setNumber(rset.getInt(1));
				oTask.setDesc(rset.getString(2));
				oTask.setStartDate(rset.getString(3));
				oTask.setEstimatedHours(rset.getInt(4));
				oTask.setExactHours(rset.getInt(5));
				oTask.setDeliverable(rset.getString(6));
				
				oTaskList.add(oTask);
				}
			rset.close();
			stetement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
		
		return oTaskList;
	}

}
